package database;
import java.sql.*;
import java.util.ArrayList;

import model.Product;
import oracle.jdbc.pool.OracleDataSource;

public class ProductOperations 
{
	private Connection conn;
	private Statement stm;
	private ResultSet rst;
	private PreparedStatement prst;
	
	public ProductOperations()
	{
		conn=openDB();
		
	}
	// open database connection
	public Connection openDB()
	{
		try 
		{
			OracleDataSource ods = new OracleDataSource();

			if ( test.Main.dataConn == "GaryTallaght"){
				ods.setURL("jdbc:oracle:thin:@//10.10.2.7:1521/global1");
				ods.setUser("X00106491");
				ods.setPassword("db13May85");
			}
			else if ( test.Main.dataConn == "KeythTallaght"){
				ods.setURL("jdbc:oracle:thin:@//10.10.2.7:1521/global1");
				ods.setUser("X00106271");
				ods.setPassword("db19Jan82");
			}
			else if ( test.Main.dataConn == "KeythHome"){
				ods.setURL("jdbc:oracle:thin:HR/localhost:1521:XE");
				ods.setUser("System");
				ods.setPassword("7481600kW");
			}
			else if ( test.Main.dataConn == "GaryHome"){
				ods.setURL("jdbc:oracle:thin:HR/SYSTEM@localhost:1521:XE");
				ods.setUser("SYSTEM");
				ods.setPassword("1nf1n1ty");
			}

			conn = ods.getConnection();
			System.out.println("connected product.");
		} 
		catch (Exception e) 
		{
			System.out.print("Unable to load driver " + e);
			System.exit(1);
		}
		return conn;
	}
	// drop product table from database
	public void dropProductTable() 
	{
		System.out.println("Checking for existing tables.");
		try
		{
			stm = conn.createStatement();

			try
			{
			// Dropping a sequence
			String dropseq = "drop sequence prod_id_seq";
			stm = conn.prepareStatement(dropseq);
			stm.executeUpdate(dropseq);
			}
			catch(SQLSyntaxErrorException e)
			{
				
			}
			try
			{
				stm.execute("DROP TABLE orderitem");
				System.out.println("orderitem table dropped.");
			}
			catch(SQLException ex)
			{
				
			}
			stm.execute("DROP TABLE Product");
			System.out.println("product table dropped.");
		} 
		catch (SQLException ex) 
			{
				System.out.println("ERROR: " + ex.getMessage());
				//ex.printStackTrace();
			}
		
	}
	// create an product table in database
	public void createProductTable() 
	{
		try 
		{
			String create = "CREATE TABLE Product "
					+ "(prod_id NUMBER PRIMARY KEY, prod_name VARCHAR(40), prod_supplier VARCHAR(40), prod_cat VARCHAR(40), prod_price number(6,2))";
			stm = conn.createStatement();
			stm.executeUpdate(create);
			System.out.println("product table created");
			// Creating a sequence
			String createseq = "create sequence prod_id_seq increment by 1 start with 1 nocache";
			stm = conn.createStatement();
			stm.executeUpdate(createseq);
		} 
		catch (SQLException e) 
		{
			System.out.print("SQL Exception " + e);
			System.exit(1);
		}
	}
	// print all products in table
	public void queryProduct() 
	{
		try 
		{
			String queryString = "SELECT * FROM Product";
			stm = conn.createStatement();
			rst = stm.executeQuery(queryString);
			System.out.println("Product Table");
			while (rst.next()) 
			{
				System.out.println(rst.getInt(1) + " " + rst.getString(2)
						+ " " + rst.getString(3)
						+ " " + rst.getString(4)
						+ " " + rst.getDouble(5));
			}
		} 
		catch (Exception e) 
		{
			System.out.println(e);
		}
	}
	// delete an product from table
	public void deleteProduct(int i) 
	{
		try 
		{
			String cmd = "DELETE FROM Product WHERE prod_id =" + i;
			stm = conn.createStatement();
			stm.executeUpdate(cmd);
		} 
		catch (Exception e) 
		{
			System.out.println(e);
		}
	}
	// add an product to the table
	public void addProduct(Product e) 
	{
		try 
		{
			String sql = "INSERT INTO Product(prod_id, prod_name, prod_supplier, prod_cat, prod_price) VALUES (prod_id_seq.nextVal,?,?,?,?)";

			prst = conn.prepareStatement(sql);
			prst.setString(1, e.getName());
			prst.setString(2, e.getSupplier());
			prst.setString(3, e.getCategory());
			prst.setDouble(4, e.getPrice());

			prst.executeUpdate();
			System.out.println("product added");
			
		} 
		catch (Exception se) 
		{
			System.out.println(se);
		}
	}
	// return a result set of all products in table
	public ResultSet getProduct() 
	{
		try 
		{
			String queryString = "SELECT * FROM Product ORDER BY prod_id";

			stm = conn.createStatement();
			rst = stm.executeQuery(queryString);
		} 
		catch (Exception e) 
		{
			System.out.println(e);
		}
		return rst;
	}
	
	//return arraylist of all products
	public ArrayList<Product> getProductList() 
	{
		ArrayList<Product> p=new ArrayList<Product>();
		Product p1;
		try 
		{
			String queryString = "SELECT * FROM Product ORDER BY prod_id";

			stm = conn.createStatement();
			rst = stm.executeQuery(queryString);
			while(rst.next())
			{
				p1=new Product(rst.getInt(1),rst.getString(2),rst.getString(3),rst.getString(4),rst.getDouble(5));
				p.add(p1);
			}
		} 
		catch (SQLException e) 
		{
			System.out.println(e);
		}
		return p;
	}
	
	// return a result set of all products with a certain prod id
		public ResultSet getProduct(int a) 
		{
			try 
			{
				String queryString = "SELECT * FROM Product where prod_id="+a;

				stm = conn.createStatement();
				rst = stm.executeQuery(queryString);
			} 
			catch (Exception e) 
			{
				System.out.println(e);
			}
			return rst;
		}
	
	//get prod id from its name
		public int getProdId(String n)
		{
			int a=0;
			try 
			{
				String queryString = "SELECT * FROM Product where prod_name='"+n+"'";

				stm = conn.createStatement();
				ResultSet rst2 = stm.executeQuery(queryString);
				while(rst2.next())
				{
					a=rst2.getInt(1);
				}
			} 
			catch (SQLException e) 
			{
				System.out.println(e);
			}
			return a;
		}
		
		// return a result set of all products with a certain category
				public ResultSet getProductCat(String n) 
				{
					try 
					{
						String queryString = "SELECT * FROM Product where prod_cat='"+n+"' order by prod_id";

						stm = conn.createStatement();
						rst = stm.executeQuery(queryString);
					} 
					catch (Exception e) 
					{
						System.out.println(e);
					}
					return rst;
				}
			
	// update a product
	public void updateProduct(int i,String n,String c,String s,double p) 
	{
		try 
		{
			String sql = "UPDATE Product SET prod_price='" + p + "' where prod_id="+ i;
			stm = conn.createStatement();
			stm.executeUpdate(sql);
			sql = "UPDATE Product SET prod_supplier='" + s + "' where prod_id="+ i;
			stm = conn.createStatement();
			stm.executeUpdate(sql);
			sql = "UPDATE Product SET prod_cat='" + c + "' where prod_id="+ i;
			stm = conn.createStatement();
			stm.executeUpdate(sql);
			sql = "UPDATE Product SET prod_name='" + n + "' where prod_id="+ i;
			stm = conn.createStatement();
			stm.executeUpdate(sql);
		} 
		catch (Exception e) 
		{
			System.out.println("Problem" + e);
		}
	}
	// returning added product
	public ResultSet getLastRow() 
	{
		String sqlStatement = "SELECT * FROM Product ORDER BY prod_id";
		try {
			prst = conn.prepareStatement(sqlStatement,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			rst = prst.executeQuery();
			rst.last();
			System.out.println(rst.getInt(1)+","+rst.getString(2)+","+rst.getString(3)+","+rst.getString(4)+","+rst.getDouble(5));
		} 
		catch (Exception ex) 
		{
			System.out.println("ERROR: " + ex.getMessage());
		}
		
		return rst;
	}
	
	// close the connection to database
	public void closeDB() 
	{
		try 
		{
			stm.close();
			rst.close();
			conn.close();
			System.out.print("Connection closed product");
		} 
		catch (SQLException e) 
		{
			System.out.print("Could not close connection ");
			e.printStackTrace();
		}
	}
	
	// close the connection to database
		public void closeDB2() 
		{
			try 
			{
				stm.close();
				//rst.close();
				conn.close();
				System.out.print("Connection closed product");
			} 
			catch (SQLException e) 
			{
				System.out.print("Could not close connection ");
				e.printStackTrace();
			}
		}
}
